<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018-07-20
 * Time: 下午 2:25\
 * excel导入导出
 */
namespace app\api\controller;
use think\Controller;
class Excel extends Controller{

    public function _initialize(){
        vendor("Classes.PHPExcel");
        vendor("Classes.PHPExcel.Writer.Excel5");
        vendor("Classes.PHPExcel.Writer.Excel2007");
        vendor("Classes.PHPExcel.IOFactory");
    }
    /**
     * @param $filename  导出的文件名字
     * @param $row 导出有几列
     * @param $title 每列的名字
     * @param $field 每列的字段名
     * @param $sheet 文件标题名称
     * @param $data 导出的数据 array()
     */
    public function out($filename='',$sheet='',$row=0,$title=array(),$field=array(),$data=array())
    {
        $strEnglish = $this->getEnglish();
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        /*--------------设置表头信息------------------*/
//        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'ID编号')
        for ($i = 0; $i < $row; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($strEnglish[$i] . '1', $title[$i]);
        }
        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        $j = 2;  //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($data);  //计算有多少条数据
        for ($j = 2; $j <= $count + 1; $j++) {
            for($i=0;$i<count($field);$i++){
                $objPHPExcel->getActiveSheet()->setCellValue($strEnglish[$i].$j, $data[$j-2][$field[$i]]);
            }
        }
        /*--------------下面是设置其他信息------------------*/

        $objPHPExcel->getActiveSheet()->setTitle($sheet);      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //Excel2003通过PHPExcel_IOFactory的写函数将上面数据写出来
        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007"); //Excel2007
        header('pragma:public');
        header("Content-Disposition:attachment;filename=$filename.xls");
        $PHPWriter->save('php://output');exit;

    }
    /**
     * 字符
     */
    public function getEnglish(){
        return array(
            '0'=>'A',
            '1'=>'B',
            '2'=>'C',
            '3'=>'D',
            '4'=>'E',
            '5'=>'F',
            '6'=>'G',
            '7'=>'H',
            '8'=>'I',
            '9'=>'J',
            '10'=>'K',
            '11'=>'L',
            '12'=>'M',
            '13'=>'N',
            '14'=>'O',
            '15'=>'P',
            '16'=>'Q',
            '17'=>'R',
            '18'=>'S',
            '19'=>'T',
            '20'=>'U',
            '21'=>'V',
            '22'=>'W',
            '23'=>'X',
            '24'=>'Y',
            '25'=>'Z',
        );
    }
}